"""
https://openpyxl.readthedocs.io/en/stable/charts/bar.html
"""
import openpyxl
from openpyxl.chart import BarChart, Reference

workbook = openpyxl.Workbook()
worksheet = workbook.active

worksheet.title = "Bar Chart"

# 准备数据
rows = [
    ('Number', 'Batch 1', 'Batch 2'),
    (2, 10, 30),
    (3, 40, 60),
    (4, 50, 70),
    (5, 20, 10),
    (6, 10, 40),
    (7, 50, 30),
]

for row in rows:
    worksheet.append(row)

# 绘制柱状图
chart = BarChart()
# 垂直、水平
chart.type = 'col'
# 设置柱状图的样式（颜色）
chart.style = 10
chart.title = 'Bar Chart'
chart.x_axis.title = 'Sample length (mm)'
chart.y_axis.title = 'Test number'

data = Reference(worksheet, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(worksheet, min_col=1, min_row=2, max_row=7)
# titles_from_data分类标题从数据中获取
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4
# 指定柱状图在Excel中的位置
worksheet.add_chart(chart, "H10")

# 保存工作簿
workbook.save('BarChart.xlsx')
